package com.utils;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.entity.OrmJdbcFile;

public class DruidTool {
	private static Logger logger = LoggerFactory.getLogger(DruidTool.class);
	
	private DruidDataSource dataSource;
	
	private DruidPooledConnection connection;
	
	private String database;
	
	public DruidTool(OrmJdbcFile jdbc) throws SQLException {
		String driver = jdbc.getDriver();
		String url = jdbc.getUrl();
		String database = jdbc.getDatabase();
		String user = jdbc.getUser();
		String password = jdbc.getPassword();
		this.database = database;
		dataSource = new DruidDataSource();
	    dataSource.setDriverClassName(driver);
	    dataSource.setName(database);
	    dataSource.setUrl(url + "?useSSL=false&serverTimezone=Asia/Shanghai&useTimezone=true");
	    dataSource.setUsername(user);
	    dataSource.setPassword(password);
	    dataSource.addConnectionProperty("connectTimeout", "6");
		
		// 初始化大小、最小、最大
		dataSource.setInitialSize(1);
		dataSource.setMinIdle(1);
		dataSource.setMaxActive(3);
		// 从连接池获取连接等待超时的时间，1000毫秒即1秒
		dataSource.setMaxWait(5000);
		//配置间隔多久启动一次DestroyThread，对连接池内的连接才进行一次检测，单位是毫秒。
		//检测时:1.如果连接空闲并且超过minIdle以外的连接，如果空闲时间超过minEvictableIdleTimeMillis设置的值则直接物理关闭。
		//2.在minIdle以内的不处理。
		dataSource.setTimeBetweenEvictionRunsMillis(3000);
		dataSource.setMinEvictableIdleTimeMillis(6000);
		dataSource.setValidationQueryTimeout(1);
		//超过时间限制是否回收
		dataSource.setRemoveAbandoned(true);
		//超时时间；单位为秒。180秒=3分钟
		dataSource.setRemoveAbandonedTimeout(360);
		//关闭abanded连接时输出错误日志
		/*dataSource.setLogAbandoned(false);
		dataSource.setBreakAfterAcquireFailure(false);
		dataSource.setConnectionErrorRetryAttempts(3);*/
		
		dataSource.setKeepAlive(true);
		
		//dataSource.init();
	    System.out.println(dataSource.toString());
	    
	    connection = dataSource.getConnection();
	    
    }
	
    public DruidDataSource getDataSource() {
        return dataSource;
    }
    
	public DruidPooledConnection getConnection() throws SQLException {
	    return connection;
	}
    
    
	public JdbcTable getTable(String table){
    	JdbcTable info = null;
		ResultSet rs = null;
		try {
			DatabaseMetaData metaData = connection.getMetaData();
			//String dbName = dataSource.getDataSourceStat().getName();
			rs = metaData.getTables(this.database, this.database, table, new String[] { "TABLE" });
		} catch (SQLException e) {
			logger.error("获取数据库表异常：{}", e);
	    	return null;
		}
		try {
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				String sql = "SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+this.database+"' AND TABLE_NAME = '"+tableName+"'";
				  ResultSet rrs = connection.createStatement().executeQuery(sql);
		            String tableRemark = null;
		            while (rrs.next()) {
		                tableRemark = rrs.getString("TABLE_COMMENT");
		            }
		            if (StrUtil.isEmpty(tableRemark)) {
		            	tableRemark = table;
					}
				//String[] commentArr = getComment(tableName);
				info = new JdbcTable(tableName, tableRemark);
			}
		} catch (SQLException e) {
			logger.error("获取数据库表失败，{}", e);
		}
    	return info;
    }
    
	/*public List<WymbTableInfo> getTables(){
		List<WymbTableInfo> tables = new ArrayList<WymbTableInfo>();
		WymbTableInfo info = null;
		ResultSet rs = null;
		try {
			DatabaseMetaData metaData = connection.getMetaData();
			String dbName = dataSource.getDataSourceStat().getName();
			rs = metaData.getTables(dbName, dbName, null, new String[] { "TABLE" });
			//release();
		} catch (SQLException e) {
			logger.error("获取数据库表异常：{}", e);
	    	return tables;
		}
		try {
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				String[] commentArr = getComment(tableName);
				info = new WymbTableInfo(tableName, commentArr[0]);
				tables.add(info);
			}
		} catch (SQLException e) {
			logger.error("获取数据库表失败，{}", e);
		}
		return tables;
	}*/
	
	public List<JdbcTableColumn> getTableCloumns(String tableName){
    	List<JdbcTableColumn> columns = new ArrayList<JdbcTableColumn>();
    	try{
            Statement stmt = connection.createStatement();
            String sql = "select column_name, column_type, data_type, column_key, is_nullable, column_comment,column_default from information_schema.columns where table_name='" + tableName + "'and table_schema='" + dataSource.getName() + "' ORDER BY ordinal_position ASC";
            JdbcTableColumn info = null;
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
            	Integer isKey = 0;
            	if(!"".equals(rs.getString("column_key")) && !rs.getString("column_key").equals(null)){
            		String PRI = rs.getString("column_key");
            		if(PRI == "PRI" || PRI.equals("PRI")){
            			isKey = 1;
            		}
            	}
            	String name = rs.getString("column_name");
            	String type = rs.getString("column_type");
            	String sqlType = rs.getString("data_type");
            	Integer isNull = rs.getString("is_nullable").equals("YES")?1:0;
            	String remark = rs.getString("column_comment");
            	remark = remark.toString().replaceAll("\\\\r", "").replaceAll("\\\\n", "").trim();
            	Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            	Matcher m = p.matcher(remark);
            	remark = m.replaceAll("");
            	String length=null;
            	if(type.indexOf("(") !=-1 && !sqlType.equals("enum")) {
            		length=type.substring(type.indexOf("(")+1, type.indexOf(")"));
            	}
            	String defaults=rs.getString("column_default");
                info = new JdbcTableColumn(isKey, name, type, sqlType, isNull, remark,length,defaults);
                columns.add(info);
            }
    		//release();
        }
        catch (SQLException e){
			logger.error("获取数据库表字段失败，{}", e);
        }
		return columns;
    }
	
	/*private String[] getComment(String tableName) {
		String[] res = new String[] {tableName, ""};
		if(StrUtil.isEmpty(tableName)) {
			return res;
		}
		try {
			ResultSet rs = connection.createStatement().executeQuery("SHOW CREATE TABLE " + tableName);
			if (rs != null && rs.next()) {
				  System.out.println(rs.getString(1));
				  System.out.println(rs.getString(2));
				  System.out.println(rs.getString(3));
				  System.out.println(rs.getString(4));
				  String create = rs.getString(2); 
				  res[0] = parse(create);
				  res[1] = create;
			 }
		} catch (SQLException e) {
			logger.error("获取数据库表备注异常:{}", e);
		}
		return res;
	}*/
	
	/*private String parse(String all) {
	    String comment = null;
	    if(all == null){
	    	return null;
	    }
	    int index = all.indexOf("COMMENT='");
	    if (index < 0) {
	        return "";
	    }
	    comment = all.substring(index + 9);
	    comment = comment.substring(0, comment.length() - 1);
	    try {
	        //comment = new String(comment.getBytes("GBK"));
	        if(comment.indexOf("InnoDB") >=0 ){
	        	comment = ""; 
	        }
	    } catch (Exception e){
			logger.error("解析异常",  e);
	    }
	    return comment;
	}*/
    
    public void close(){
    	dataSource.close();
    }

    public void release() {
        try {
        	if(null != connection){
            	if(!connection.isClosed()) {
            		connection.close();
                	logger.error("连接已经关闭了");
            	}
            }
        } catch (SQLException e) {
        	logger.error("Druid连接释放异常：{}", e);
        }
    }
}
